package com.jabc;

import org.apache.commons.lang3.StringUtils;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class PostgreSQLJDBC {
    public static void main(String[] args) throws DocumentException {
        String path="D:\\gdp.xml";
        SAXReader reader = new SAXReader();
        Document document = reader.read(new File(path));
        Element root = document.getRootElement();

        Iterator it = root.elementIterator();
        List< YearGDP > yearGDPList=new ArrayList<>();
        while (it.hasNext()) {
            Element element = (Element) it.next();//record
            Iterator recordIt = element.elementIterator();
            String countryCode="";
            String countryName="";
            String indicator_code="";
            String indicator_name="";
            String year="";
            String gdp="0";
            YearGDP gdpBean=new YearGDP();
            while(recordIt.hasNext()){
                Element record = (Element) recordIt.next();
                List<Attribute>  attributeList=record.attributes();
                if(attributeList.size()==2){
                    if(attributeList.get(0).getValue().equals("Country")){
                         countryCode=attributeList.get(1).getValue();
                         countryName=record.getText();
                    }
                    if(attributeList.get(0).getValue().equals("Item")){
                         indicator_code=attributeList.get(1).getValue();
                         indicator_name=record.getText();
                    }
                }
                if(attributeList.size()==1){
                    if(attributeList.get(0).getValue().equals("Year")){
                         year=record.getText();
                    }
                    if(attributeList.get(0).getValue().equals("Value")){
                         gdp= StringUtils.isBlank(record.getText())?"0":record.getText();
                    }
                }
            }
            gdpBean.setCountry_name(countryName);
            gdpBean.setCountry_code(countryCode);
            gdpBean.setGdp(Double.parseDouble(gdp));
            gdpBean.setIndicator_code(indicator_code);
            gdpBean.setIndicator_name(indicator_name);
            gdpBean.setYear(Integer.parseInt(year));
            yearGDPList.add(gdpBean);
            if(yearGDPList.size()==200){
                insert(yearGDPList);
            }
        }
        if(yearGDPList.size()>0){
            insert(yearGDPList);
        }
    }
    /**
     * @method getConn() 获取数据库的连接
     * @return Connection
     */
    public static Connection getConn() {
        String driver = "org.postgresql.Driver";
        String url = "jdbc:postgresql://localhost:5432/testdb";
        String username = "postgresuser";
        String password = "postgresuserwd";
        Connection conn = null;
        try {
            Class.forName(driver); // classLoader,加载对应驱动
            conn =DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * @method insert(Student student) 往表中插入数据
     * @return int 成功插入数据条数
     */
    public static void  insert(List<YearGDP> gdpList) {
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into country_gdp_year (country_name,country_code,indicator_name,indicator_code,year,gdp) values(?,?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt =conn.prepareStatement(sql);
            for(YearGDP gdp:gdpList){
                pstmt.setString(1, gdp.getCountry_name());
                pstmt.setString(2, gdp.getCountry_code());
                pstmt.setString(3, gdp.getIndicator_name());
                pstmt.setString(4, gdp.getIndicator_code());
                pstmt.setInt(5, gdp.getYear());
                pstmt.setDouble(6, gdp.getGdp());
                pstmt.addBatch();
            }
            gdpList.clear();
            pstmt.executeBatch();
            pstmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}